1. Field of the Invention
The present invention relates to a method, system, and program for managing database operations with respect to a database table.
2. Description of the Related Art
Data records in a relational database management system (RDBMS) in a computer are maintained in tables, which are a collection of rows all having the same columns. Each column maintains information on a particular type of data for the data records which comprise the rows. One or more indexes may be associated with each table. An index is an ordered set of pointers to data records in the table based on the data in one or more columns of the table. In some cases, all the information needed by a query may be found in the index, making it unnecessary to search the actual table. An index is comprised of nodes which include an index key and a pointer to a database record in the table having the key column values of the index entry key. Index keys are stored in a sorted order in the index and the pointers associated with the key values provide a mechanisms for fast access of associated rows in the table in the sorted order of the index. Columns that are used frequently to access a table may be used as key columns. Using an index to search and access rows in the associated table substantially improves query performance.
Database tables and indexes are stored in a table and index spaces, respectively. The table and index spaces may be organized in multiple partitions. A single table space can have one or more tables. If the table space is partitioned, then there is only one table that can be implemented in the multiple partitions. When a database table is created, a partition map may be created for the table that is used to determine in which partition a row in the table is stored when the table is stored in multiple partitions. The user may specify in the partition map one or more partition map columns and specify a mapping of the partition map column values to partitions by specifying ranges of values. A partition map column is a column of the table row whose value determines in which partition the row is stored. The definition of the partition map would indicate criteria for determining which partition to store in a row based on the one or more column values in the table row that correspond to partition map columns.
A key value comprises a concatenation of the index key column values from one record in the table. For instance, the partition map may map different ranges of partition column values to different partitions, such that a new row being added to the table whose column value falls within one specified range of values is stored in the partition associated with that range.
In current art, an index on a database table that is not the partition map comprises a non-partitioned secondary index (NPSI). The NPSI comprises one index that provides an ordering of all rows in the database table according to one or more key columns of the table. When performing queries on the index, the entire index is scanned to determine key column values that satisfy the query search predicates, which then produces the table rows identified by the index nodes whose key column values satisfy the search predicates.